import csv
import mysql.connector
from mysql.connector import errorcode
from datetime import datetime

# --- 数据库配置 (与 app.py 中的一致) ---
DB_CONFIG = {
    'user': 'bookkeeper',
    'password': '12345678', # 你的 MySQL 'bookkeeper' 用户密码
    'host': 'localhost',
    'database': 'bookkeeping_db' # 你创建的数据库名
}
# CSV 文件路径，现在使用你提供的文件名
CSV_FILE_PATH = 'expenses_data.csv' 

def import_data():
    """从 CSV 文件导入数据到 MySQL 数据库的 expenses 表。"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        with open(CSV_FILE_PATH, mode='r', encoding='utf-8-sig') as csvfile: # 使用 utf-8-sig 处理 BOM
            csv_reader = csv.DictReader(csvfile) 
            
            insert_query = """
                INSERT INTO expenses (date, amount, main_category, sub_category, payment_method, notes)
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            
            records_to_insert = []
            line_num = 1 
            for row in csv_reader:
                line_num += 1
                try:
                    date_str = row['时间']
                    amount_str = row['金额']
                    main_category = row['大分类']
                    sub_category = row.get('二级分类', '') 
                    payment_method = row.get('支付方式', '')
                    notes = row.get('备注', '')

                    date_obj = datetime.strptime(date_str, '%Y/%m/%d') 
                    formatted_date = date_obj.strftime('%Y-%m-%d')
                    
                    amount = float(amount_str)
                    
                    record = (
                        formatted_date,
                        amount,
                        main_category,
                        sub_category if sub_category else None,
                        payment_method if payment_method else None,
                        notes if notes else None
                    )
                    records_to_insert.append(record)
                except ValueError as ve:
                    print(f"跳过行 {line_num} (数据格式错误): {row} - 错误: {ve}")
                    continue
                except KeyError as ke:
                    print(f"跳过行 {line_num} (CSV表头缺失或不匹配): '{ke}' 键未找到。行数据: {row}")
                    continue
                except Exception as e:
                    print(f"处理行 {line_num} 时发生未知错误: {row} - 错误: {e}")
                    continue

            if records_to_insert:
                cursor.executemany(insert_query, records_to_insert)
                conn.commit()
                print(f"成功导入 {cursor.rowcount} 条记录到 expenses 表。")
            else:
                print("CSV文件中没有可导入的有效记录。")

    except FileNotFoundError:
        print(f"错误: CSV 文件 '{CSV_FILE_PATH}' 未找到。请确保它在正确的路径下 (`~/Linuxtest6/`)。")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("数据库访问被拒绝：请检查 `import_csv_to_mysql.py` 和 `app.py` 中的用户名或密码是否正确。")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print(f"数据库 '{DB_CONFIG['database']}' 不存在。请先创建数据库或运行 `flask init-db-mysql`。")
        else:
            print(f"数据库连接或操作错误: {err}")
    except Exception as e:
        print(f"导入数据时发生未知错误: {e}")
    finally:
        if cursor:
            cursor.close()
        if conn and conn.is_connected():
            conn.close()

if __name__ == '__main__':
    print(f"准备从 '{CSV_FILE_PATH}' 导入数据到 MySQL 数据库 '{DB_CONFIG['database']}'...")
    import_data()
